﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using Microsoft.SqlServer.Management.Smo;
using Microsoft.SqlServer.Management.Common;
using System.Data.SqlClient;
using System.Data.Sql;



namespace SqlBackUp
{
    public partial class Form1 : Form
    {
        private static Server srvSql;
        

        public Form1()
        {
            InitializeComponent();
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            
            DataTable dtServers = SmoApplication.EnumAvailableSqlServers(true);
            
            if (dtServers.Rows.Count > 0)
            {   
                
                foreach (DataRow drServer in dtServers.Rows)
                {
                    
                    cmbServer.Items.Add(drServer["Name"]);
                   
                }
            }
            
            cmbServer.Items.Add(dtServers.Rows[0]["server"]);
            groupBox2.Enabled = false;
            groupBox3.Enabled = false;
            groupBox4.Enabled = false;
            
        }

     
        private void btnConnect_Click(object sender, EventArgs e)
        {
            try
            {
                if (cmbServer.SelectedItem != null && cmbServer.SelectedItem.ToString() != "")
                {

                    groupBox2.Enabled = true;
                    groupBox3.Enabled = true;
                    groupBox4.Enabled = true;
                   
                    ServerConnection srvConn = new ServerConnection(cmbServer.SelectedItem.ToString());
               
                    srvConn.LoginSecure = false;
                
                    srvConn.Login = txtUsername.Text;
                
                    srvConn.Password = txtPassword.Text;
               
                    srvSql = new Server(srvConn);
                    cmb_Database.Items.Clear();
                    lb_Table.Items.Clear();
                    lb_View.Items.Clear();
                    lb_Sp.Items.Clear();

                    foreach (Database dbServer in srvSql.Databases)
                    {
                   
                        cmb_Database.Items.Add(dbServer.Name);
                        
                    }
                    cmbDevice.Items.Clear();
                    foreach (BackupDevice bkDevice in srvSql.BackupDevices)
                    {
                        cmbDevice.Items.Add(bkDevice.Name);

                    }
            
                           
         
                    
                    }
                else
                {
                
                    MessageBox.Show("Please select a server first", "Server Not Selected", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
                }
            }
            catch (SmoException ex)
                {
                    MessageBox.Show(ex.Message);
                }
             
        }

        private void btnCreate_Click(object sender, EventArgs e)
        {
            try
            {
               
                if (srvSql != null)
                {
                    
                    if (cmbDevice.Enabled == false)
                    {
                        if (saveBackupDialog.ShowDialog() == DialogResult.OK)
                        {
                            
                            Backup bkpDatabase = new Backup();


                            
                            bkpDatabase.Action = BackupActionType.Database;
                           
                            bkpDatabase.Database = cmb_Database.SelectedItem.ToString();

                                             
                            BackupDeviceItem bkpDevice = new BackupDeviceItem(saveBackupDialog.FileName, DeviceType.File);

                           

                            bkpDatabase.Devices.Add(bkpDevice);
                            
                            bkpDatabase.SqlBackup(srvSql);
                            progressBar1.Visible = true;
                            for (int i = progressBar1.Minimum; i <= progressBar1.Maximum; i++)
                            {
                                progressBar1.PerformStep();

                            }
                            progressBar1.Visible = false;


                        }

                    }
                    else
                    {

                        srvSql.ConnectionContext.ExecuteScalar(@"backup database " + cmb_Database.SelectedItem.ToString() + " to ["
                                                                + cmbDevice.SelectedItem.ToString() + "]");
                        progressBar1.Visible = true;
                        progressBar1.Refresh();
                        for (int i = progressBar1.Minimum; i <= progressBar1.Maximum; i++)
                        {
                            progressBar1.PerformStep();

                        }

                        progressBar1.Visible = false;
                    }
                }
                else
                {
                   
                    MessageBox.Show("A connection to a SQL server was not established.", "Not Connected to Server", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
                }
            }
            catch (SmoException ex)
            {
                MessageBox.Show(ex.Message);
            }
        }
        
        
        
        private void btnRestore_Click(object sender, EventArgs e)
        {
            try
            {
                
                if (srvSql != null)
                {
                   
                    if (cmbDevice.Enabled == false)
                    {
                        if (openBackupDialog.ShowDialog() == DialogResult.OK)
                        {
                            
                            Restore rstDatabase = new Restore();
                           
                            rstDatabase.Action = RestoreActionType.Database;
                            
                            rstDatabase.Database = cmb_Database.SelectedItem.ToString();

                            
                            BackupDeviceItem bkpDevice = new BackupDeviceItem(openBackupDialog.FileName, DeviceType.File);

                           
                            rstDatabase.Devices.Add(bkpDevice);


                            
                            rstDatabase.ReplaceDatabase = true;
                           
                            rstDatabase.SqlRestore(srvSql);
                            progressBar1.PerformStep();

                        }
                    }
                    else
                    {
                        srvSql.ConnectionContext.ExecuteScalar(@"restore database " + cmb_Database.SelectedItem.ToString() + " from ["
                                                           + cmbDevice.SelectedItem.ToString() + "]" +" with file = "+dataGridView1.CurrentCell.Value.ToString());
                        
                        progressBar1.Visible = true;
                        progressBar1.Value = 1;
                        for (int i = progressBar1.Minimum; i <= progressBar1.Maximum; i++)
                        {
                            progressBar1.PerformStep();

                        }
                        
                        progressBar1.Visible = false;
                        

                    }
                }
                else
                {
                   
                    MessageBox.Show("A connection to a SQL server was not established.", "Not Connected to Server", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
                }
            }
            catch(SmoException  ex) 
            {
                MessageBox.Show(ex.Message);
            }
        }

        
    
        private void btn_Database_Click(object sender, EventArgs e)
        {
            cmb_Database.Items.Clear();
            
            foreach (Database dbServer in srvSql.Databases)
            {
                
                cmb_Database.Items.Add(dbServer.Name);

            } 
        }

        private void btn_Table_Click(object sender, EventArgs e)
        {
            try
            {
                if (srvSql != null)
                {
                    Database dtb = srvSql.Databases[cmb_Database.SelectedItem.ToString()];
                    lb_Table.Items.Clear();

                    foreach (Microsoft.SqlServer.Management.Smo.Table tb in dtb.Tables)
                    {
                        lb_Table.Items.Add(tb.Name);
                    }

                }
                else
                {

                    MessageBox.Show("A connection to a SQL server was not established.", "Not Connected to Server", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
                }
            }
            catch (SmoException ex)
            {
                MessageBox.Show(ex.Message);
            }

        }

        private void btn_View_Click_1(object sender, EventArgs e)
        {
            if (srvSql != null)
            {
                Database dtb = srvSql.Databases[cmb_Database.SelectedItem.ToString()];                
                lb_View.Items.Clear();             

                
                foreach (Microsoft.SqlServer.Management.Smo.View v in dtb.Views)
                {
                    
                    lb_View.Items.Add(v.Name);
                    
                }
                
            }
            else
            {
                
                MessageBox.Show("A connection to a SQL server was not established.", "Not Connected to Server", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
            }

        }

        private void btn_Sp_Click(object sender, EventArgs e)
        {
            if (srvSql != null)
            {
                Database dtb = srvSql.Databases[cmb_Database.SelectedItem.ToString()];                
                lb_Sp.Items.Clear();
                
                foreach (Microsoft.SqlServer.Management.Smo.StoredProcedure s in dtb.StoredProcedures)
                {
                    lb_Sp.Items.Add(s.Name);

                }
            }
            else
            {
                
                MessageBox.Show("A connection to a SQL server was not established.", "Not Connected to Server", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
            }

        }

        private void btn_Device_Click(object sender, EventArgs e)
        {
            try
            {
                if (saveBackupDialog.ShowDialog() == DialogResult.OK)
                {
                    srvSql.ConnectionContext.ExecuteScalar("sp_addumpdevice @devtype='disk', @logicalname='" + tb_DeviceName.Text + "', @physicalname='" + saveBackupDialog.FileName + "'");
                }
                cmbDevice.Items.Clear();
                foreach (BackupDevice bkDevice in srvSql.BackupDevices)
                {
                    cmbDevice.Items.Add(bkDevice.Name);

                }
            }
            catch (SmoException ex)
            {
                MessageBox.Show(ex.Message);
            }

            
            
        }

        private void btn_Preview_Click(object sender, EventArgs e)
        {
            if (cmbDevice.Enabled == true)
            {

                try
                {
                    if (cmbDevice.SelectedItem!=null&&cmbDevice.SelectedItem.ToString() != "")
                    {
                        Database db = srvSql.Databases["msdb"];

                        string Sqlcomm = @"SELECT     dbo.backupset.position as [vitri], dbo.backupset.database_name AS [Database], dbo.backupset.server_name AS Server, dbo.backupmediafamily.logical_device_name AS [Device Type], 
                      dbo.backupmediafamily.physical_device_name AS Location, dbo.backupset.backup_start_date AS [Start Date], 
                      dbo.backupset.backup_finish_date AS [Finish Date]
FROM         dbo.backupset INNER JOIN
                      dbo.backupmediafamily ON dbo.backupset.media_set_id = dbo.backupmediafamily.media_set_id
WHERE     (dbo.backupmediafamily.logical_device_name = '" + cmbDevice.SelectedItem.ToString() + "')";
                        if (Sqlcomm.Replace(" ", "").ToUpper().StartsWith("SELECT"))
                        {
                            DataSet ds = db.ExecuteWithResults(Sqlcomm);
                            dataGridView1.DataSource = ds.Tables[0];
                        }
                    }
                    else
                    {
                        MessageBox.Show("Chưa chọn Device", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
                    }
                }
                catch (SmoException  ex)
                {
                    MessageBox.Show(ex.Message);
                }
                
                
                                
            }
            else
            {
                if (openBackupDialog.ShowDialog() == DialogResult.OK) 
                {
                     try
                     {
                         Database db = srvSql.Databases["msdb"];

                         string Sqlcomm = @"SELECT     dbo.backupset.database_name AS [Database], dbo.backupset.server_name AS Server, dbo.backupmediafamily.logical_device_name AS [Device Type], 
                      dbo.backupmediafamily.physical_device_name AS Location, dbo.backupset.backup_start_date AS [Start Date], 
                      dbo.backupset.backup_finish_date AS [Finish Date]
FROM         dbo.backupset INNER JOIN
                      dbo.backupmediafamily ON dbo.backupset.media_set_id = dbo.backupmediafamily.media_set_id
WHERE     (dbo.backupmediafamily.physical_device_name = '"+ openBackupDialog.FileName +"')";
                         if (Sqlcomm.Replace(" ", "").ToUpper().StartsWith("SELECT"))
                         {
                             DataSet ds = db.ExecuteWithResults(Sqlcomm);
                             dataGridView1.DataSource = ds.Tables[0];
                         }
                     }
                     catch (SmoException ex)
                     {
                         MessageBox.Show(ex.Message);
                     }
                }
            }
                
        }

        

        private void radioButton1_CheckedChanged(object sender, EventArgs e)
        {
            
            cmbDevice.Enabled = true;
        }

        private void radioButton2_CheckedChanged(object sender, EventArgs e)
        {
            cmbDevice.Enabled = false;
            
        }

       
        

        

        

        

        
    }
}